package com.hbnu.dao;

import com.hbnu.entity.User;
import com.hbnu.entity.UserFile;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import java.util.stream.Collectors;

import static com.hbnu.tools.FileTool.initUserFileMap;
import static com.hbnu.tools.PrintTool.println;

public class FunctionDao extends JDBC{
    //所有删除的用户文件
    public List<UserFile> selectUserFileDeleted() {
        try (Statement statement = getStatement()){
            String sql="select u.user_name,f.fid,f.fname,f.is_file from users u join " +
                    "(select user_id,fid,fname,is_file from files_folders where is_deleted = 1) f " +
                    "on u.user_id = f.user_id;";
            List<UserFile> list=new ArrayList<>();
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                UserFile userFile=new UserFile();
                userFile.setUname(resultSet.getString(1));
                userFile.setFid(resultSet.getInt(2));
                userFile.setFname(resultSet.getString(3));
                userFile.setIs_file(resultSet.getBoolean(4));
                list.add(userFile);
            }
            return list;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    /*
    -- 删除
update users set is_deleted = 1 where user_id = '0af7e771-335e-46d2-8060-0bb9a39edbc4';
-- 恢复
update users set is_deleted = 0 where user_id = '0af7e771-335e-46d2-8060-0bb9a39edbc4';
     */
    public void updateUserDeleted(String uid,int is_deleted) {
        try (PreparedStatement preparedStatement = getPreparedStatement(
                "update users set is_deleted = ? where user_id = ?"
        )){
           preparedStatement.setInt(1,is_deleted);
           preparedStatement.setString(2,uid);
            if(preparedStatement.executeUpdate()<=0)
                throw new RuntimeException("数据未修改！");

        } catch (SQLException e) {
            throw new RuntimeException("sql异常");
        }

    }

    public void addUserSpace(String uid, int size) {
        try (PreparedStatement preparedStatement = getPreparedStatement(
                "update users set space = space + ? where user_id = ?"
        )){
            int g=1073741824;
            long l= (long) size *g;
            preparedStatement.setLong(1,l);
            preparedStatement.setString(2,uid);
            if(preparedStatement.executeUpdate()<=0)
                throw new RuntimeException("数据未修改！");

        } catch (SQLException e) {
            throw new RuntimeException("sql异常");
        }
    }

    public void deleteUserFile() {
        try (Statement statement = getStatement()){
            String sql="delete from files_folders where is_deleted = 1;";
            if(statement.executeUpdate(sql)<=0)
                println("无数据删除");
            else
                println("删除成功！");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public Map<Integer,String> getUselessFilesId() {
        try (Statement statement = getStatement()){
            String sql="select file_id,file_path from files fs " +
                    "where not exists " +
                    "(" +
                    "select 1 from files_folders ff where ff.file_id = fs.file_id " +
                    " );";
            ResultSet resultSet = statement.executeQuery(sql);
            Map<Integer,String> map=new HashMap<>();
            while (resultSet.next()){
                map.put(resultSet.getInt(1),resultSet.getString(2));
            }
            return map;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //删除服务器文件
    public boolean deleteServerFile(Set<Integer> idSet) {
        String sql="delete from files where file_id in ";
        try (Statement statement = getStatement()){
            connection.setAutoCommit(false);
            StringBuilder stringBuilder=new StringBuilder();
            String ids = idSet.stream()
                    .map(String::valueOf)
                    .collect(Collectors.joining(","));
            stringBuilder.append(sql).append("(").append(ids).append(");");
            return statement.executeUpdate(stringBuilder.toString())>0;
        } catch (SQLException e) {
            throw new RuntimeException("sql异常");
        }
    }

    //删除用户 先设置根为null 再删除根文件 再删除用户
    public void deleteUser(User user) {
        try (PreparedStatement preparedStatement = getPreparedStatement(
                "delete from users where user_id = ?"
        )){
            connection.setAutoCommit(false);
            //根地址设置为null
            if(!setRootNull(user.getUid())){
                println("数据异常！");
                connection.rollback();
                return;
            }
            //删除文件
            List<Integer> rootList = getRootList(user.getFid());
            if(!deleteUserFile(rootList)){
                println("用户文件删除失败！");
                connection.rollback();
                return;
            }
            //删除用户
            preparedStatement.setString(1,user.getUid());
            if(preparedStatement.executeUpdate()<=0){
                println("用户删除失败！");
                connection.rollback();
                return;
            }

            connection.commit();
            println("删除成功！");

        } catch (Exception e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            throw new RuntimeException(e);
        }finally {
            try {
                connection.setAutoCommit(true);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    public List<Integer> getRootList(int fid) {
        try (PreparedStatement preparedStatement = getPreparedStatement(
                "call select_root_fid(?);"
        )){
            preparedStatement.setInt(1,fid);
            ResultSet resultSet = preparedStatement.executeQuery();
            List<Integer> idList=new ArrayList<>();
            while (resultSet.next()){
                idList.add(resultSet.getInt(1));
            }
            return idList;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public boolean deleteUserFile(List<Integer> rootList) {
        String sql="delete from files_folders where fid in ";
        try (Statement statement = getStatement()){
            StringBuilder stringBuilder=new StringBuilder();
            String ids = rootList.stream()
                    .map(String::valueOf)
                    .collect(Collectors.joining(","));
            stringBuilder.append(sql).append("(").append(ids).append(");");
            return statement.executeUpdate(stringBuilder.toString())>0;
        } catch (SQLException e) {
            throw new RuntimeException("sql异常");
        }
    }
    public boolean setRootNull(String uid) {
        try (PreparedStatement preparedStatement = getPreparedStatement(
                "update users set fid = null where user_id = ?"
        )){
            preparedStatement.setString(1,uid);
            return preparedStatement.executeUpdate()>0;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }




    //所有删除的用户文件
    public Map<Integer, UserFile>  selectUserFileDeleted(String uid) {
        try (PreparedStatement preparedStatement = getPreparedStatement(
                "select fid,pid,fname,file_id,is_file " +
                        "from files_folders " +
                        "where user_id = ? and is_deleted = 1;"
        )){
            preparedStatement.setString(1,uid);
            return initUserFileMap(preparedStatement.executeQuery());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    public long sumFileSize(List<Integer> fileIdList) {
        try(Statement statement = getStatement()){
            String sql=
                    "select sum(file_size) " +
                            "from files " +
                            "where file_id in (";
            String ids = fileIdList.stream()
                    .map(String::valueOf) // 将 Integer 转为 String
                    .collect(Collectors.joining(","));
            ResultSet resultSet = statement.executeQuery(sql+ids+");");
            if (resultSet.next()){
                return resultSet.getLong(1);
            }
            return -1;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //更改文件夹的根 数据恢复 空间扣除
    public void rollback(String uid,int root, int fid,Long size) {
        try (PreparedStatement preparedStatement = getPreparedStatement(
                "update files_folders " +
                        "set pid = ? " +
                        "where fid = ?"
        )){
            connection.setAutoCommit(false);
            preparedStatement.setInt(1,root);
            preparedStatement.setInt(2,fid);
            if(preparedStatement.executeUpdate()<=0){
                println("文件修改异常！");
                connection.rollback();
                return;
            }
            if(!rollbackFolder(fid)){
                println("文件修改异常！");
                connection.rollback();
                return;
            }
            //修改用户存储空间
            reduceUserSpace(uid,size);
            connection.commit();
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            throw new RuntimeException(e);
        }finally {
            try {
                connection.setAutoCommit(true);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    //恢复文件
    public boolean rollbackFolder(int id) {
        try(PreparedStatement preparedStatement=getPreparedStatement(
                "call update_folder_delete_status(?,0);"
        )){
            preparedStatement.setInt(1,id);
            return preparedStatement.executeUpdate()>0;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    //扣除空间
    public void reduceUserSpace(String uid, long size) {
        try (PreparedStatement preparedStatement = getPreparedStatement(
                "update users set size = size + ? where user_id = ?"
        )){
            preparedStatement.setLong(1,size);
            preparedStatement.setString(2,uid);
            if(preparedStatement.executeUpdate()<=0)
                throw new RuntimeException("数据未修改！");

        } catch (SQLException e) {
            throw new RuntimeException("sql异常");
        }
    }

    //删除完成
    public void deleteOver(boolean ok) {
        try {
            if (ok) {
                connection.commit();
            } else
                connection.rollback();
        }catch (Exception e){
            println("数据库异常");
        }finally {
            try {
                connection.setAutoCommit(true);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

    }
}
